package com.yusvn.scm.goods.bi.service;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

import com.jfinal.aop.Inject;
import com.jfinal.kit.Kv;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.yusvn.scm.admin.service.LoginService;
import com.yusvn.scm.common.BaseService;
import com.yusvn.scm.common.constant.Const;
import com.yusvn.scm.common.kit.RetKit;

import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Workbook;



/**
 * BI报表
 * @author liugz
 *
 */
public class ScmBiService extends BaseService {

	@Inject
	LoginService loginService;
	
	//获取EXCEL文件名称
	public String getFileName(String bbmc) {
		String fileName = Db.queryStr("select dict_data_name as fileName from sys_dictionary_data "
				+ "where dict_data_code=? and dict_id=(select dict_id from sys_dictionary where dict_code='bi_sql') ", bbmc);
		return fileName + ".xlsx";
	}
	
	//获取EXCEL文件路径
	public String getFilePath(String fileName) {
		return "D:/download/temp/"+fileName; //生成excel的路径
	}
	
	//生成临时表名
	public String getTableName(String bbmc, Kv kv) {
		return "scmbi_"+bbmc; //+"_"+kv.getStr("userid")
	}
	
	
	//获取生成报表数据的时间
	public String getTableTime(String bbmc, Kv kv) {
		//表名
		String tableName = getTableName(bbmc, kv);
		
		String tableTime = "未生成报表数据";
		try {
			tableTime = Db.use(Const.SCMDB).templateByString("select to_char(min(creatat), 'yyyy-mm-dd hh24:mi:ss') from "+tableName, kv).queryStr();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return tableTime;
	}
	
	//生成报表数据
	public RetKit createTable(String bbmc, Kv kv) {
		//表名
		String tableName = getTableName(bbmc, kv);
		//获取sql语句
		String sqls = Db.queryStr("select comments as sqls from sys_dictionary_data "
				+ "where dict_data_code=? and dict_id=(select dict_id from sys_dictionary where dict_code='bi_sql') ", bbmc);
		if (!kv.isEmpty()){
			String regexDate1 = "#\\(date1\\)";
			// 替换 #(date2) 的正则表达式
			//String regexDate2 = "#\\(date2\\)";
			// 要替换成的字符串
			String replacementDate1 = kv.get("data1").toString().equals("zhijie") ? "19990101" : kv.get("data1").toString();
			String replacementDate2 = kv.get("data2").toString().equals("zhijie") ? "21000101" : kv.get("data2").toString();

			// 创建 Pattern 对象
			Pattern patternDate1 = Pattern.compile(regexDate1);
			//Pattern patternDate2 = Pattern.compile(regexDate2);

			// 创建 Matcher 对象
			Matcher matcherDate1 = patternDate1.matcher(sqls);
			//Matcher matcherDate2 = patternDate2.matcher(sqls);
			String replacedString = matcherDate1.replaceAll(replacementDate1)
					.replace("#(date2)", replacementDate2);
			sqls = replacedString;
		}

		try {
			//先删除表
			Db.use(Const.SCMDB).templateByString("drop table "+tableName, kv).update();
		} catch (Exception e) {
			e.printStackTrace();
		}

		try {
			//再查询创建表
			Db.use(Const.SCMDB).templateByString("create table "+tableName+" as "+sqls, kv).update();
		} catch (Exception e) {
			e.printStackTrace();
			return RetKit.fail("生成报表数据失败");
		}
		
		return RetKit.ok("生成报表数据成功");
	}
	
	// 通用获取分页数据
	public Page<Record> getPages(String bbmc, Kv kv) {
		//表名
		String tableName = getTableName(bbmc, kv);
		
		//获取品牌权限
		List<String> qxList = getPpqx(kv.getStr("erpUsercode"));
		kv.set("qx_pp", qxList);
		
		String sqls = "select * from "+tableName +" where 1=1 "
				+ "    #if(qx_pp) "  // 品牌权限
				+ "        and ("
				+ "            #for(x : qx_pp)"
				+ "                "+tableName+".ppid = '#(x)' "
				+ "                #(for.last ? '' : 'or')"
				+ "            #end "
				+ "        )"
				+ "    #end"
				+ "    #if(cxspbh) "
				+ "        and ("
				+ "                "+tableName+".款号_260 like '%'||upper( '#(cxspbh)' )||'%' "
				+ "                or "+tableName+".商品名称_270 like '%'||upper( '#(cxspbh)' )||'%' "
				+ "        )"
				+ "    #end"
				+ "    #if(gysid) "
				+ "        and ("
				+ "            #for(x : gysid)"
				+ "                "+tableName+".供应商_230 = '#(x)' "
				+ "                #(for.last ? '' : 'or')"
				+ "            #end "
				+ "        )"
				+ "    #end"
				+ "    #if(cdid) "
				+ "        and ("
				+ "            #for(x : cdid)"
				+ "                "+tableName+".门店名称_110 = '#(x)' "
				+ "                #(for.last ? '' : 'or')"
				+ "            #end "
				+ "        )"
				+ "    #end"
				+ "    #if(ppid) "
				+ "        and ("
				+ "            #for(x : ppid)"
				+ "                "+tableName+".区域_120 = '#(x)' "
				+ "                #(for.last ? '' : 'or')"
				+ "            #end "
				+ "        )"
				+ "    #end"
				+ "    #if(nfid) "
				+ "        and ("
				+ "            #for(x : nfid)"
				+ "                "+tableName+".年份_170 = '#(x)' "
				+ "                #(for.last ? '' : 'or')"
				+ "            #end "
				+ "        )"
				+ "    #end"
				+ "    #if(jjid) "
				+ "        and ("
				+ "            #for(x : jjid)"
				+ "                "+tableName+".季节_180 = '#(x)' "
				+ "                #(for.last ? '' : 'or')"
				+ "            #end "
				+ "        )"
				+ "    #end"
				+ "    #if(dlid) "
				+ "        and ("
				+ "            #for(x : dlid)"
				+ "                "+tableName+".大类_140 = '#(x)' "
				+ "                #(for.last ? '' : 'or')"
				+ "            #end "
				+ "        )"
				+ "    #end"
				+ "    #if(zlid) "
				+ "        and ("
				+ "            #for(x : zlid)"
				+ "                "+tableName+".中类_150 = '#(x)' "
				+ "                #(for.last ? '' : 'or')"
				+ "            #end "
				+ "        )"
				+ "    #end"
				+ "    #if(xlid) "
				+ "        and ("
				+ "            #for(x : xlid)"
				+ "                "+tableName+".小类_160 = '#(x)' "
				+ "                #(for.last ? '' : 'or')"
				+ "            #end "
				+ "        )"
				+ "    #end"
				+ "    #if(xbid) "
				+ "        and ("
				+ "            #for(x : xbid)"
				+ "                "+tableName+".性别_130 = '#(x)' "
				+ "                #(for.last ? '' : 'or')"
				+ "            #end "
				+ "        )"
				+ "    #end"
				+ "    #if(bdid) "
				+ "        and ("
				+ "            #for(x : bdid)"
				+ "                "+tableName+".波段_190 = '#(x)' "
				+ "                #(for.last ? '' : 'or')"
				+ "            #end "
				+ "        )"
				+ "    #end"
				+ "    #if(sql) "
				+ "        and ("
				+ "        "+tableName+".售罄率_870 >= '#(sql)' "
				+ "        )"
				+ "    #end"
				+ "    #if(cgcs) "
				+ "        and ("
				+ "        "+tableName+".采购次数_360 >= '#(cgcs)' "
				+ "        )"
				+ "    #end"
				+ "    #if(dhts) "
				+ "        and ("
				+ "        "+tableName+".最早采购天_430 <= '#(dhts)' "
				+ "        )"
				+ "    #end"
				+ "";
		
		// 查询结果到页面
		return Db.use(Const.SCMDB).templateByString(sqls, kv).paginate(getPage(kv), getLimit(kv));
	}

	// 通用获取所有数据
	public List<Record> getList(String bbmc, Kv kv) throws Exception {
		//表名
		String tableName = getTableName(bbmc, kv);
		
		//从生成的临时表中取数
		String sqls = "select * from "+tableName; //+" where rownum<=10"
		
		// 查询结果到页面
		return Db.use(Const.SCMDB).templateByString(sqls, kv).find();
	}
	
	// 通用获取单条数据
	public Record get(String bbmc, Kv kv) throws Exception {
		//表名
		String tableName = getTableName(bbmc, kv);
		
		return Db.use(Const.SCMDB).findFirst("select * from "+tableName+" where ROWNUM=1");
	}
	
	// 通用获取总行数
	public Long getTotal(String bbmc, Kv kv) throws Exception {
		//表名
		String tableName = getTableName(bbmc, kv);
		
		return Db.use(Const.SCMDB).queryLong("select count(1) as cnt from "+tableName);
	}
	
	//生成EXCEL
	public RetKit createExcel(String bbmc, Kv kv) {
		String fileName = getFileName(bbmc); //文件名称
		String filePath = getFilePath(fileName); //文件路径
		
		try {
			//先删除文件
			boolean isDel = FileUtil.del(filePath); 
			if(isDel) {
				//再读取数据写入Excel
				return doExcel(bbmc, kv, filePath);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return RetKit.fail("生成EXCEL失败："+e.getMessage());
		}
		return RetKit.fail("生成EXCEL失败");
	}

	//读数到Excel
	public RetKit doExcel(String bbmc, Kv kv, String filePath) {
		//获取总页数
		long totalRow=0;
		try {
			kv.set("limit", 1); //先便于统计总行数
			totalRow = getPages(bbmc, kv).getTotalRow(); //getTotal(bbmc, kv); //getTotal() 拼接查询条件有问题
		} catch (Exception e2) {
			e2.printStackTrace();
		}
//		System.out.println("totalRow:"+totalRow);
		//获取标题
		List<Record> tableColumn = new ArrayList<Record>();
		Record rec = null;
		try {
			rec = get(bbmc, kv);
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		
		String[] columnNameArr = rec.getColumnNames();
		int columnNameLen = columnNameArr.length;
		for(int i=0; i<columnNameLen; i++) {
			String[] colArr = columnNameArr[i].split("_");
			Record colRec = new Record();
			colRec.set("fieldcode", columnNameArr[i]); //字段编号
			colRec.set("fieldname", colArr[0]); //字段名称
			colRec.set("fieldseq", 1); //字段显示排序
			if(colArr.length>1) {
				colRec.set("fieldseq", colArr[1]);
			}
//			colRec.set("showwidth", 87);
//			if(colArr.length>2) {
//				colRec.set("showwidth", colArr[1]);
//			}
			tableColumn.add(colRec);
		}
		
		//排序字段
		tableColumn = tableColumn.stream().sorted((r1, r2) -> {
							return r1.getInt("fieldseq") - r2.getInt("fieldseq");
						}).collect(Collectors.toList());
		
		//通过工具类创建writer
		//ExcelWriter writer = ExcelUtil.getWriter(fileName);
		BigExcelWriter writer= ExcelUtil.getBigWriter(filePath, "表1"); //大量数据输出
		
	    
		//自定义标题别名
		tableColumn.forEach(r -> {
			writer.addHeaderAlias(r.getStr("fieldcode"), r.getStr("fieldname"));
		});
		writer.addHeaderAlias("creatat", "数据时间");
		//writer.setColumnWidth(0,20).setColumnWidth(1,20).setColumnWidth(2,30); //设置列宽
		
		writer.disableDefaultStyle(); //禁用默认样式(即原样输出)
		
		System.out.println("==开始："+DateUtil.date(System.currentTimeMillis()) );
		//每个子表存放行数
		long sheetRow = 1000000;
		//每次查询行数
		long limit = 5000;
		//当前是查询的第几页
		long page = 0;
		kv.set("limit", limit);
		
//		totalRow = 155; //设置个测试总行数 TODO
		
		//需要拆分成多少个子表
		long sheetCnt = (long)Math.ceil(1.0*totalRow/sheetRow); //向上取整
		if(sheetCnt<=1) sheetCnt=1;
		
		//拆分成多个子表 
		for(int s=1; s<=sheetCnt; s++) {
			writer.setSheet("表"+s);
			
			//最后一个子表计算要分多少次读取
			if(s == sheetCnt) {
				sheetRow = totalRow % sheetRow;
			}
			
			//每个子页分多少页进行读取
			long pageCnt = (long)Math.ceil(1.0*sheetRow/limit); //向上取整
			if(pageCnt<=1) pageCnt=1;
			
			//导出（获取导出的数据）
			for(int i=1; i<=pageCnt; i++) {
				page++;
				kv.set("page", page);
				
				List<Map<String,Object>> mapList = new LinkedList<>();
				Page<Record> recList = null;
				try {
					//kv.set("limit", 2);
					recList = getPages(bbmc, kv);
				} catch (Exception e) {
					e.printStackTrace();
				}
//					System.out.println("读数"+i+"："+DateUtil.date(System.currentTimeMillis()) );
				recList.getList().forEach(record -> {
					Map<String, Object> columnMap = record.getColumns();
					//columnMap.remove("creatat"); //数据时间
					columnMap.remove("序号"); //排序号
					columnMap.remove("rownum_");
					columnMap.remove("ppid"); //品牌ID
					columnMap.remove("cdid"); //仓店ID
				    mapList.add(columnMap);
				});
//					System.out.println("转换"+i+"："+DateUtil.date(System.currentTimeMillis()) );
		        
				//一次性写出内容，强制输出标题
				writer.write(mapList, i==1 ? true : false);

				recList = null;
				mapList.clear();
				System.out.println("==结束"+page+"："+DateUtil.date(System.currentTimeMillis()) );
			}
		}
		writer.close(); //关闭writer，释放内存
		System.out.println("==完成："+DateUtil.date(System.currentTimeMillis()) );
		
		return RetKit.ok("生成EXCEL成功");
	}
	
	// 读数到Excel把0变成自定义格式#导出来看不见
	public RetKit doExcel1(String bbmc, Kv kv, String filePath) {
	    
		//获取标题
		List<Record> tableColumn = new ArrayList<Record>();
		Record rec = null;
		try {
			rec = get(bbmc, kv);
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		
		String[] columnNameArr = rec.getColumnNames();
		int columnNameLen = columnNameArr.length;
		for(int i=0; i<columnNameLen; i++) {
			String[] colArr = columnNameArr[i].split("_");
			Record colRec = new Record();
			colRec.set("fieldcode", columnNameArr[i]); //字段编号
			colRec.set("fieldname", colArr[0]); //字段名称
			colRec.set("fieldseq", 1); //字段显示排序
			if(colArr.length>1) {
				colRec.set("fieldseq", colArr[1]);
			}
//			colRec.set("showwidth", 87);
//			if(colArr.length>2) {
//				colRec.set("showwidth", colArr[1]);
//			}
			tableColumn.add(colRec);
		}
		
		//排序字段
		tableColumn = tableColumn.stream().sorted((r1, r2) -> {
			return r1.getInt("fieldseq") - r2.getInt("fieldseq");
		}).collect(Collectors.toList());
		
		//实例化excel
		 ExcelWriter writer = ExcelUtil.getWriter(filePath, "表1"); // 数据输出
		
		//自定义标题别名
	   tableColumn.forEach(r -> {
			   writer.addHeaderAlias(r.getStr("fieldcode"), r.getStr("fieldname"));
	       });
	   
				writer.addHeaderAlias("creatat", "数据时间");
				//writer.setColumnWidth(0,20).setColumnWidth(1,20).setColumnWidth(2,30); //设置列宽
				
				writer.disableDefaultStyle(); //禁用默认样式(即原样输出)

	    List<Map<String, Object>> mapList = new LinkedList<>();
	    List<Record> recList = null;
	    try {
	        recList = getList(bbmc, kv);
	    } catch (Exception e) {
	        e.printStackTrace();
	    }

	    recList.forEach(record -> {
	        Map<String, Object> columnMap = record.getColumns();
	        columnMap.remove("序号"); //排序号
			columnMap.remove("rownum_");
			columnMap.remove("ppid"); //品牌ID
			columnMap.remove("cdid"); //仓店ID
	        
	     // Replace "正常" values with empty string
			   // columnMap.replaceAll((key, value) -> "正常".equals(value) ? "": value);
				
				// 判断是否包含空值或null，如果不包含则添加到mapList
			  //  if (columnMap.values().stream().anyMatch(value ->value!= null && !value.toString().trim().isEmpty() )) {
			        mapList.add(columnMap);
			  //  }
	    });

	    // 一次性写出内容，强制输出标题
	    writer.write(mapList, true);

	    // 获取 Workbook 对象
	    Workbook workbook = writer.getSheet().getWorkbook();

	    // 创建一个新的 CellStyle
	    CellStyle newCellStyle = workbook.createCellStyle();

	    // 设置数据格式为#
	    newCellStyle.setDataFormat(workbook.createDataFormat().getFormat("#"));
	    
	    for (int e = 0; e <= writer.getColumnCount(); e++) {
	        for (int j = 0; j <=writer.getRowCount(); j++) {
	            Cell cell = writer.getCell(e, j);
	            if (cell != null && cell.getCellType() == CellType.NUMERIC && cell.getNumericCellValue() == 0) {
	                cell.setCellStyle(newCellStyle);
	            }
	        }
	    }
	    
	    recList = null;
	    mapList.clear();

	    writer.close(); // 关闭writer，释放内存

	    return RetKit.ok("生成EXCEL成功");
	}
	
	//定时生成EXCEL
	public void execExcel() {
		String bbmc = "jxcbb"; //报表名称
		Kv kv = Kv.create().set("erpUsercode", "000"); //默认管理员
		
		try {
			//先调用生成数据方法
			RetKit ret = createTable(bbmc, kv);
			//再生成EXCEL
			if(ret.isOk())
				createExcel(bbmc, kv);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
